<?php
	require_once 'error.php';
	// 获取数据库连接
  	$mysqli =  new mysqli( "localhost", "site_admin", "site_admin", "site_db" );
  	
  	function connect_db(){
	  	// 连接数据库
	  	if( mysqli_connect_errno() )
		{
			echo "MySQL connect error";
			exit();
		}
  	}
//==================用户管理=======================//
	function retrive_user_info( $username ){
		global  $mysqli;
		// 查询数据
		$user = null;
		$query = "select * from site_users where username='".$username."'";
		$mysqli->query("set names 'utf8'");
		if( $result = $mysqli->query($query))
		{
			$user = $result->fetch_assoc();
			
			$result->free();
		}else{
			site_die("获取用户信息失败，请重试");
		} 
		
		return $user;
	}	
	
	/**
	 * 更新用户信息
	 * @param string $name
	 * 		用户名
	 * @param striing $nick_name
	 * 		昵称
	 * @param string $email
	 * 		email
	 * @param string $pwd
	 * 		md5加密的密码
	 * @return boolean
	 * 		成功与否
	 */
	function update_userinfo( $name, $nick_name, $email, $pwd ){
		global $mysqli;
		$result = null;
		$query = "update site_users set nick_name='".$nick_name."', email='".$email."'";
		if( $pwd!='' ){
			$query .= ", password='".md5($pwd)."'";
		}
		$query .= " where username='".$name."'";
		$mysqli->query("set names 'utf8'");
		if( $result = $mysqli->query($query) ){
			//echo $result->num_rows."行已删除";
			return true;
		}else 
			return false;
	}

//==================Post 管理=====================//	
	/**
	 * 找到特定类别的文章列表(只查找active的列表)
	 * @param string $category 
	 * @return 
	 * 	查找到的列表为id-title的数组
	 */
	function get_titles_by_category( $category, $status=1 ){
		global  $mysqli;
		// 查询数据
		$alist = array();
		$query = "select p.id, p.title, p.post_date, p.modify_date, p.visit_times, p.category from site_posts p where p.status=".$status;
		if( $category != '' ) 
			$query .= " and p.category='".$category."'";
		$mysqli->query("set names utf8");
		if($stmt = $mysqli->prepare($query))
		{
			$stmt->execute();
			$stmt->bind_result($p_id, $p_title, $p_date, $p_mod_date, $p_visit_time, $p_category);
			$artNum = $stmt->num_rows;
			while( $stmt->fetch() ){
				$one_row = array( 'id'=>$p_id, 'title'=>$p_title, 'post_date'=>$p_date, 
						'modify_date'=>$p_mod_date, 'visit_times'=>$p_visit_time, 'category'=>$p_category );
				$alist[$p_id] = $one_row;
			}
			$stmt->close();
		}else {
			echo "prepare xxquery error:".$stmt->errno;
		}
		return $alist;
	}
	
	/**
	 * 范围为id的文章，
	 * @param string $id
	 * 		文章id
	 * @param string $category
	 * 		文章分类
	 * @return 找到的文章，数组形式
	 * 		
	 */
	function get_post_by_id( $id ){
		global $mysqli;
		// 查询一篇特定的文章
	  	$pos = array();
		$query = "select p.*, u.nick_name from site_posts p, site_users u";
	  	$query .= " where p.author=u.id and p.status=1 and p.id=".$id;
	  	$mysqli->query("set names utf8");
	  	if( $result = $mysqli->query($query) ){
  			$one_row = $result->fetch_assoc();
  			$result->free();
	  	}else 
	  		site_die("prepare query error:".$stmt->error);
	  	return $one_row;
	}
	
	/**
	 * 发表新文章
	 * @param int $author
	 * 		author id
	 * @param string $title
	 * 		title
	 * @param string $content
	 * 		content
	 * @param string $category
	 * 		分类
	 * @param string $post_date
	 * 		要按照mysql规定的格式
	 * @return boolean result
	 * 		成功与否
	 */
	function add_post( $author, $title, $content, $category, $post_date ){
		global $mysqli;
		$result = FALSE;
		$query = "insert into site_posts( author, title, content, post_date, modify_date, status, visit_times, category )";
		$query .= " values( ".$author.", '".$title."','".$content."','".$post_date."','".$post_date."', 1, 0,'".$category."' )";
		$mysqli->autocommit(TRUE);
		$mysqli->query("set names 'utf8'");
		$mysqli->query($query);
		if( $mysqli->affected_rows > 0 ){
			$result = TRUE;
		}else {
			site_die("保存失败，可能的原因:".$mysqli->error);
		}
			
		return $result;
	}
	
	function update_post($id,  $title, $content, $category, $mod_date  )
	{
		global $mysqli;
		$upsql = "update site_posts set title='".$title."', content='".$content."', category='".$category."', modify_date='".$mod_date."'";
		$upsql .= " where id=".$id;
		$mysqli->query("set names 'utf8'");
		if( $result = $mysqli->query($upsql) ){
			echo "title:".$title;
			return TRUE;
		}else {
			site_die( "更新文章失败，可能的原因:".$mysqli->error);
			return FALSE;
		}
	}
	
	/**
	 * 删除文章，逻辑上
	 * @param int $id
	 * 	id
	 */
	function del_post_by_id( $id ){
		global $mysqli;
		$query = "update site_posts set status=2 where id=".$id;
		$mysqli->query("set names 'utf8'");
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
	
	/**
	 * 恢复删除的文章
	 * @param int $id
	 * 	id
	 */
	function restore_post_by_id($id){
		global $mysqli;
		$result = null;
		$query = "update site_posts set status=1 where id=".$id;
		$mysqli->query("set names 'utf8'");
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
	
	/**
	 * 彻底删除文章
	 * @param int $id
	 * 		文章的id 
	 */
	function destroy_post_by_id( $id ){
		global $mysqli;
		$query = "delete from site_posts where id=".$id;
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
//=======================feedback功能-====================//

//============Feedback页面分页实现===================//	
	/**
	 * 1. 返回feedback的总数
	 * @param unknown_type $mysqli
	 */
	function get_fdbak_count( ){
		global $mysqli;
		$cou = 0;
		$query = "select count(*) from site_feedback t where t.msg_status=1";
		if( $result = $mysqli->query( $query ) ){
			$row = $result->fetch_array();
			$cou = $row[0];
		}
		
		return $cou;
	}
	
	/**
	 * 按照参数返回一定数目的feedback
	 * @param int $offset
	 * 		偏移量  默认0 即第一行
	 * @param int $maxf
	 * 		返回的最大数目 默认10
	 * @param string $author 
	 * 		根据author返回,要返回全部请传递 ''值
	 * @param int $status
	 * 		留言状态
	 * @return 
	 * 		返回array(array())
	 * 		其中第一维用index索引，第二层用assoc索引(msger_name, msg_content, msg_date)
	 */
	function get_feedback( $offset, $maxf, $author, $status=1  ){
		global $mysqli;
		$fbs = array();
		$query = "select * from site_feedback t where t.msg_status=".$status;
		if( $author != '' ){
			$query.=" and t.msger_name='".$author."'";
		}
		$query .= " order by t.msg_date desc";
		if( $maxf != -1 )
			$query .= " limit ".$offset.", ".$maxf;
		$mysqli->query("set names utf8");
		$index = 0;
		if( $result = $mysqli->query($query) ){
			while( $tmp = $result->fetch_assoc() ){
				$fbs[$index] = $tmp;
				$index++;
			}
			$result->free();
		}
		return $fbs;
	}
	
	/**
	 * 保存留言信息
	 * @param unknown_type $mysqli
	 * 	数据库连接
	 * @param unknown_type $fb
	 *  连接
	 */
	function commit_feedback( $fb ){
		global $mysqli;
		$result = false;
		
		$now_date = date("Y-m-d H:i:s", mktime());
		$ins = "insert into site_feedback(msger_name, msger_mail, msger_phone, msg_other, msg_content, msg_date, msg_status, msger_ip)";
		$ins .= " values('".$fb['author']."','".$fb['email']."','".$fb['phone']."','".$fb['other']."','".$fb['comment']."','".$now_date."',2, "; // 待审批
		$ins .= "'".$fb['msger_ip']."')";
		$mysqli->autocommit(TRUE);
		$mysqli->query("set names 'utf8'");
		$mysqli->query($ins);
		if( $mysqli->affected_rows > 0 )
			$result = TRUE;
		else {
			echo "error:".$mysqli->error;
			$result = FALSE;
		}
		return $result;
	}
	
	/**
	 * 检测重复ping
	 * Enter description here ...
	 * @param unknown_type $fb
	 */
	function check_feedback($fb){
		global $mysqli;
		$have = true;
		$query = "select count(*) from site_feedback where msger_name='".$fb['author']."' and msg_content='".$fb['comment']."'";
		if( $fb['email'] != '' )
			$query .= "and msger_mail='".$fb['email']."'";
		if( $fb['phone'] != '' )
			$query .= "and msger_phone='".$fb['phone']."'";
		$mysqli->query("set names 'utf8'");
		if( $rs = $mysqli->query($query) ){
			$row = $rs->fetch_array(MYSQLI_NUM);
			if( $row[0] == 0 )
				$have = false;
		}else 
			site_die("检测评论失败, 请重试", "发生错误");
		return $have;
	}
	
	function del_fb_by_id( $id ){
		global $mysqli;
		$query = "update site_feedback set msg_status=3 where id=".$id;
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
	
	function pass_fb_by_id( $id ){
		global $mysqli;
		$result = null;
		$query = "update site_feedback set msg_status=1 where id=".$id;
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
	
	function destroy_fb_by_id($id){
		global $mysqli;
		$result = null;
		$query = "delete from site_feedback where id=".$id;
		if( $result = $mysqli->query($query) ){
			return true;
		}else 
			return false;
	}
	
	
	/**
	 * 关闭数据库连接
	 */
	function close_db( ){
		global $mysqli;
		$mysqli->close();
	}
	

?>